from django.shortcuts import render

from django.http import HttpResponse
from app02 import models

from django.db.models import Avg,Max,Min,Count,Sum,F,Q #   引入函数

# Create your views here.
def xxx(request, year):
    print(year, "年")
    return HttpResponse("app02收到消息")

#F()是动态取值 的函数 ，如下内容是为了所有的书涨价100
def findxxx(request):
    res=models.Book.objects.filter(Q(price__gt=500)|Q(title__startswith="天")).values("title","price")
    print(res)
    return HttpResponse(res)




def addprice(request):
    res = models.Book.objects.update(price=F("price") + 100)
    print(res)
    return HttpResponse(res)

def groupcount(request):
    # SELECT province,COUNT(id) FROM app02_emp GROUP BY province
    #  values(分组字段) 是group by 分组字段， annotate(聚合函数) count(*)
    res = models.Emp.objects.values("province").annotate(p_count=Count("province"))
    print(res)
    return HttpResponse(res)

#分组
def groupmax(request):
    #每个省的最高工资
    res = models.Emp.objects.values("province").annotate(in_price=Max("salary"))

    print(res)
    return HttpResponse(res)


#聚合
def countminmax(request):
    res = models.Book.objects.aggregate(c=Count("id"), max=Max("price"), min=Min("price"))
    print(res, type(res))
    return HttpResponse(res['max'])



def bookavg(request):
    res = models.Book.objects.aggregate(Avg("price"))
    print(res, type(res))
    return HttpResponse(res['price__avg'])


def find(request):
    addr = models.AuthorDetail.objects.filter(addr="黑木崖").first()
    # 一对一的反向，用 对象.小写类名 即可，不用加 _set
    res = addr.author.name
    print(res, type(res))
    return HttpResponse(" 关联查询： ok住在{0}的作者有：{1}".format(addr,res))



def delrelation(request):
    author_obj = models.Author.objects.get(id=3)
    book_obj = models.Book.objects.get(id=8)
    author_obj.book_set.remove(book_obj)
    return HttpResponse(" remove relation ok")


def delrelation2(request):
    # book_obj = models.Book.objects.get(id=7)
    # book_obj.authors.clear()

    author_obj = models.Author.objects.get(id=3)
    # author_obj.book.clear() #这个操作不能执行，因为没有配置反向的book映射
    author_obj.book_set.clear()
    return HttpResponse("remove relation all ok")

def addAuthor4(request):
    pub = models.Publish.objects.filter(name="明教出版社").first()
    wo = models.Author.objects.filter(name="任我行").first()
    # create()：创建一个新的对象，并同时将它添加到关联对象集之中。
    book = wo.book_set.create(title="吸星大法", price=300, pub_date="1999-9-19", publish=pub)
    print(book, type(book))
    return HttpResponse(book)


def addAuthor3(request):
    ying = models.Author.objects.filter(name="任盈盈").first()
    book = models.Book.objects.filter(pk=1).first()
    #调用作者属性——书的集合？反向添加作者
    ying.book_set.add(book)
    return HttpResponse(book)

def addAuthor2(request):
    book_obj = models.Book.objects.get(id=2)
    author_list = models.Author.objects.filter(id__gt=2)
    book_obj.authors.add(*author_list)  # 将 id 大于2的作者对象添加到这本书的作者集合中

    # 方式二：传对象 id
    book_obj = models.Book.objects.get(id=7)
    book_obj.authors.add(*[1, 3])  # 将 id=1 和 id=3 的作者对象添加到这本书的作者集合中
    return HttpResponse(book_obj)

def addAuthor(request):
    #  获取作者对象
    chong = models.Author.objects.filter(name="令狐冲").first()
    ying = models.Author.objects.filter(name="任盈盈").first()
    #  获取书籍对象
    book = models.Book.objects.filter(pk=8).first()
    #  给书籍对象的 authors 属性用 add 方法传作者对象
    book.authors.add(chong, ying)
    return HttpResponse(book)

def addbook(request):
    #  获取出版社对象
    # pub_obj = models.Publish.objects.filter(pk=2).first()
    #  给书籍的出版社属性publish传出版社对象
    # book = models.Book.objects.create(title="独孤九剑", price=500, pub_date="2021-10-1", publish=pub_obj)
    # book = models.Book.objects.create(title="九阴真经", price=500, pub_date="2021-10-1", publish=pub_obj)
    ###################################################################################################

    #  获取出版社对象的id
    # pub_obj = models.Publish.objects.filter(pk=1).first()
    # pk = pub_obj.pk
    #  给书籍的关联出版社字段 publish_id 传出版社对象的id
    pk = 1
    book = models.Book.objects.create(title="冲灵剑法", price=100, pub_date="2004-04-04", publish_id=pk)
    print(book, type(book))
    return HttpResponse(book)